--ESTABLISH PHONE TYPE TABLE
DECLARE @PHONE_TYPE AS TABLE (ID INT, PHONE_TYPE CHAR(1))
INSERT INTO @PHONE_TYPE (ID, PHONE_TYPE) VALUES
(1,'D'),(2,'E'),(3,'F')

--ESTABLISH VENDOR TABLE
DECLARE @VENDORS AS TABLE (ID INT, VENDOR_NAME VARCHAR(max))
INSERT INTO @VENDORS (ID, VENDOR_NAME) VALUES
(1,'APPLE'),(2,'DELL'),(3,'GOOGLE')

--ESTABLISH PHONE NUMBERS FOR VENDOR TABLE
DECLARE @PHONES AS TABLE (ID INT, VENDOR_ID INT, PHONE_TYPE_ID INT, PHONE CHAR(10))
INSERT INTO @PHONES (ID,VENDOR_ID,PHONE_TYPE_ID,PHONE) VALUES
(1,1,2,'5096638673'),
(2,1,3,'5096638674'),
(3,3,1,'5096638675'),
(4,3,3,'5096638676')


;WITH
PRECEDENCE AS --ESTABLISH PHONE PRECEDENCE PER PHONE TYPE
(
    SELECT  PT.ID,
		  PT.PHONE_TYPE,
		  CASE PT.PHONE_TYPE
			 WHEN 'F' THEN 1
			 WHEN 'E' THEN 2
			 WHEN 'D' THEN 3
		  END AS Precedence
    FROM	  @PHONE_TYPE AS PT   
), --RANK EACH OF THE VENDORS PHONE NUMBERS ORDERED BY THIER PRECEDENCE
PH_RANK AS
(
    SELECT  DENSE_RANK() OVER (PARTITION BY P.VENDOR_ID ORDER BY PR.Precedence ASC) AS TheRank,
		  P.ID,P.VENDOR_ID,P.PHONE_TYPE_ID,P.PHONE,PR.Precedence
    FROM	  @PHONES AS P
		  INNER JOIN PRECEDENCE AS PR ON (P.PHONE_TYPE_ID = PR.Precedence)
)
SELECT  V.ID,
	   V.VENDOR_NAME,
	   P.PHONE
FROM	   @VENDORS AS V
	   LEFT JOIN PH_RANK AS P ON (V.ID = P.VENDOR_ID AND P.TheRank = 1)
ORDER BY	  V.VENDOR_NAME ASC
	   

